#!/usr/bin/perl

use DBI;
use strict;

my $dsn="dbi:Pg:dbname=flux_monitoring;host=localhost;port=5432";
my $user="postgres";
my $password="";

print "[main] update_calibrate_table.pl\n\n";

# Set up a connection to the database listing the RPFITS files that need to be
# processed.
my $dbh=DBI->connect($dsn,$user,$password,{ RaiseError => 0, AutoCommit => 1});

# Query for all calibrators from the extracted_files table
print "[main] querying for extracted files...\n";
my $phc=$dbh->prepare("SELECT * FROM extracted_files");
$phc->execute;

while (my ($rpf_file,$rpf_path,$rpf_project,$rpf_source,$rpf_obstime,
	   $rpf_fits,$rpf_start,$rpf_end,$rpf_freq1,$rpf_freq2)=$phc->fetchrow()){
#    print "got time $rpf_obstime\n";
    # we want to add this file to the calibrator table so we know to do
    # some reduction on it, but we don't want to add it if it already exists.
    # thankfully, the filename needs to be unique, so trying to enter the same
    # file again will result in an error
    print "[main] entering initial data into calibration table...\n";
    my $clb_string="INSERT INTO calibration VALUES('$rpf_file','$rpf_source',".
	"'$rpf_project','$rpf_obstime',$rpf_freq1,$rpf_freq2,".
	"NULL,NULL,NULL,NULL,"."NULL,NULL,NULL,NULL,"."NULL,NULL,NULL,NULL,".
	"NULL,NULL,NULL,NULL,"."FALSE,FALSE,FALSE,FALSE,"."NULL,NULL,NULL,NULL".
	")";
    print "[main/debug] $clb_string\n";
    my $clb=$dbh->prepare($clb_string);
    $clb->execute;
    $clb->finish;
    print "[main] initial data entered\n";
}
$phc->finish;
print "[main] extracted file query complete\n\n";

# the next thing to do is go through the calibrator database and for each
# phase calibrator, search through the database for flux calibrators close to it
# in time
print "[main] querying calibration table...\n";
my $alc=$dbh->prepare("SELECT file,source,obstime,freq_if1,freq_if2 FROM calibration");
$alc->execute;

while (my ($cal_file,$cal_source,$cal_obstime,$cal_freq1,$cal_freq2)=$alc->fetchrow()){
    # is this source a phase calibrator?
    print "[main/debug] found calibrator $cal_source @ $cal_obstime, frequency $cal_freq1/$cal_freq2\n";
    my $chp=$dbh->prepare("SELECT name,type FROM calibrators WHERE name='$cal_source'");
    my $is_phase_calibrator=0;
    $chp->execute;
    while (my ($source_cal,$type_cal)=$chp->fetchrow()){
	if (($cal_source eq $source_cal)&&($type_cal eq "p")){
#	    print "[main/debug] $cal_source is a phase calibrator\n";
	    $is_phase_calibrator=1;
	}
    }
    $chp->finish;
    if ($is_phase_calibrator==1){
	print "[main] $cal_source is a phase calibrator\n";
	# OK, search around this calibrator for primary calibrators and
	# potential bandpass calibrators
	my $bandpass_search;
	if (($cal_freq1>1000)&&($cal_freq1<2000)){
	    $bandpass_search="21cm";
	} elsif (($cal_freq1>2000)&&($cal_freq1<3000)){
	    $bandpass_search="13cm";
	} elsif (($cal_freq1>5000)&&($cal_freq1<7000)){
	    $bandpass_search="6cm";
	} elsif (($cal_freq1>7000)&&($cal_freq1<11000)){
	    $bandpass_search="3cm";
	} elsif (($cal_freq1>14000)&&($cal_freq1<27000)){
	    $bandpass_search="12mm";
	} elsif (($cal_freq1>27000)&&($cal_freq1<55000)){
	    $bandpass_search="7mm";
	} elsif (($cal_freq1>80000)&&($cal_freq1<110000)){
	    $bandpass_search="3mm";
	}
	print "[main] searching for a $bandpass_search bandpass calibrator...\n";
	my $search_param="obstime-'$cal_obstime'";
	my $nbf_query="select *,($search_param),extract(epoch from ($search_param)),".
	    "abs(extract(epoch from ($search_param))) from calibration".
	    " WHERE freq_if1=$cal_freq1 AND freq_if2=$cal_freq2 order by 33";
	print "[main/debug] executing query $nbf_query\n";
	my $nbf=$dbh->prepare($nbf_query);
	$nbf->execute;
	my $primary_after=1;
	my $primary_before=1;
	my $is_bandpass_calibrator=0;
	while (my @nbf_result=$nbf->fetchrow()){
	    print "[main] found source $nbf_result[1] at time difference $nbf_result[31]\n";
	    my $chp=$dbh->prepare("SELECT name,type,bandpass_$bandpass_search FROM calibrators".
				  " WHERE name='$nbf_result[1]'");
	    $chp->execute;
	    my $is_flux_calibrator=0;
	    while (my ($source_cal,$type_cal,$bp_pot)=$chp->fetchrow()){
		if (($nbf_result[1] eq $source_cal)&&($type_cal eq "f")){
		    $is_flux_calibrator=1;
		}
		if (($nbf_result[1] eq $source_cal)&&($bp_pot==1)){
		    if ($is_bandpass_calibrator==0){
			$is_bandpass_calibrator=1;
		    }
		}
	    }
	    $chp->finish;
	    if ($is_flux_calibrator==1){
		# is it before or after?
		print "[main] $nbf_result[1] is a flux calibrator\n";
		my $pnum=0;
		if ($nbf_result[31]>0){
		    # after
		    print "[main] flux calibrator is after source\n";
		    # is there an acal event between the phase and flux calibrators?
		    print "[main] searching for an intervening calibration event...\n";
		    my $ace_query="SELECT * FROM events WHERE (event_type='acal' OR event_type='dcal')".
			" AND (event_time BETWEEN '".$cal_obstime."' AND '".$nbf_result[3]."') AND ".
			"(if1_freq=".$cal_freq1." AND if2_freq=".$cal_freq2.")";
		    print "[main/debug] executing query $ace_query\n";
		    my $nres=$dbh->do($ace_query);
		    if ($nres==0){
			$pnum=2+$primary_after;
			print "[main] will insert as primary $pnum (after main observations)\n";
			$primary_after++;
		    } else {
			print "[main] found $nres calibration events\n";
		    }
		    print "[main] completed search for intervening calibration events\n";
		} else {
		    # before
		    print "[main] flux calibrator is before source\n";
		    # is there an acal event between the phase and flux calibrators?
		    print "[main] searching for an intervening calibration event...\n";
		    my $ace_query="SELECT * FROM events WHERE (event_type='acal' OR event_type='dcal')".
			" AND (event_time BETWEEN '".$nbf_result[3]."' AND '".$cal_obstime."') AND ".
			"(if1_freq=".$cal_freq1." AND if2_freq=".$cal_freq2.")";
		    print "[main/debug] executing query $ace_query\n";
		    my $nres=$dbh->do($ace_query);
		    if ($nres==0){
			$pnum=3-$primary_before;
			print "[main] will insert as primary $pnum (before main observations)\n";
			$primary_before++;
		    } else {
			print "[main] found $nres calibration events\n";
		    }
		    print "[main] completed search for intervening calibration events\n";
		}
		if (($pnum>0)&&($pnum<5)){
		    # insert the values into the table
		    print "[main] updating calibration table with primary calibration information...\n";
		    my $inq="UPDATE calibration SET primary$pnum\_file='$nbf_result[0]',".
			"primary$pnum\_source='$nbf_result[1]',".
			"primary$pnum\_project='$nbf_result[2]',".
			"primary$pnum\_time='$nbf_result[3]'".
			" WHERE file='$cal_file'";
		    print "[main/debug] executing query $inq\n";
		    my $fin=$dbh->prepare($inq);
		    $fin->execute;
		    $fin->finish;
		    print "[main] calibration table update complete\n";
		}
	    }
	    if ($is_bandpass_calibrator==1){
		# insert the values into the table
		print "[main] $nbf_result[1] is a bandpass calibrator\n";
		print "[main] updating calibration table with bandpass calibration information...\n";
		my $inq="UPDATE calibration SET bandpass_file='".$nbf_result[0]."',".
		    "bandpass_source='".$nbf_result[1]."',".
		    "bandpass_project='".$nbf_result[2]."',".
		    "bandpass_time='".$nbf_result[3]."'".
		    " WHERE file='".$cal_file."'";
		print "[main/debug] executing query $inq\n";
		my $fin=$dbh->prepare($inq);
		$fin->execute;
		$fin->finish;
		print "[main] calibration table update complete\n";
		$is_bandpass_calibrator=2; # don't set anymore bandpass calibrators
	    }
	}
	$nbf->finish;
	print "[main] bandpass calibrator search complete\n\n";
    } else {
	print "[main] $cal_source is not a phase calibrator\n";
	# we search for a bandpass calibrator for each flux calibrator
	my $bandpass_search;
	if (($cal_freq1>1000)&&($cal_freq1<2000)){
	    $bandpass_search="21cm";
	} elsif (($cal_freq1>2000)&&($cal_freq1<3000)){
	    $bandpass_search="13cm";
	} elsif (($cal_freq1>5000)&&($cal_freq1<7000)){
	    $bandpass_search="6cm";
	} elsif (($cal_freq1>7000)&&($cal_freq1<11000)){
	    $bandpass_search="3cm";
	} elsif (($cal_freq1>14000)&&($cal_freq1<27000)){
	    $bandpass_search="12mm";
	} elsif (($cal_freq1>27000)&&($cal_freq1<55000)){
	    $bandpass_search="7mm";
	} elsif (($cal_freq1>80000)&&($cal_freq1<110000)){
	    $bandpass_search="3mm";
	}
	print "[main] searching for a $bandpass_search bandpass calibrator...\n";
	my $search_param="obstime-'$cal_obstime'";
	my $nbf_query="select *,($search_param),extract(epoch from ($search_param)),".
	    "abs(extract(epoch from ($search_param))) from calibration".
	    " WHERE freq_if1=$cal_freq1 AND freq_if2=$cal_freq2 order by 33";
	print "[main/debug] executing query $nbf_query\n";
	my $nbf=$dbh->prepare($nbf_query);
	$nbf->execute;
	my $is_bandpass_calibrator=0;
	while (my @nbf_result=$nbf->fetchrow()){
	    print "[main] found source $nbf_result[1] at time difference $nbf_result[32]\n";
	    my $chp=$dbh->prepare("SELECT name,type,bandpass_$bandpass_search FROM calibrators".
				  " WHERE name='$nbf_result[1]'");
	    $chp->execute;
	    while (my ($source_cal,$type_cal,$bp_pot)=$chp->fetchrow()){
		if (($nbf_result[1] eq $source_cal)&&($bp_pot==1)){
		    if ($is_bandpass_calibrator==0){
			$is_bandpass_calibrator=1;
		    }
		}
	    }
	    $chp->finish;
	    if ($is_bandpass_calibrator==1){
		print "[main] $nbf_result[1] is a bandpass calibrator\n";
		# insert the values into the table
		print "[main] updating calibration table with primary bandpass calibration information...\n";
		my $inq="UPDATE calibration SET bandpass_file='".$nbf_result[0]."',".
		    "bandpass_source='".$nbf_result[1]."',".
		    "bandpass_project='".$nbf_result[2]."',".
		    "bandpass_time='".$nbf_result[3]."'".
		    " WHERE file='".$cal_file."'";
		print "[main/debug] executing query $inq\n";
		my $fin=$dbh->prepare($inq);
		$fin->execute;
		$fin->finish;
		print "[main] calibration table update complete\n";
		$is_bandpass_calibrator=2; # don't set anymore bandpass calibrators
	    }
	}
	$nbf->finish;
	print "[main] bandpass calibrator search complete\n\n";
    }
}
$alc->finish;
print "[main] calibration query complete\n\n";

$dbh->disconnect;

print "[main] update_calibrate_table.pl finished\n\n";
